/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tamnhat.entity;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
public class ContentTemplate implements Serializable {

    private String id;
    private String name;
    private String status;

    public ContentTemplate() {
    }

    public ContentTemplate(String id, String name, String status) {
        this.id = id;
        this.name = name;
        this.status = status;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return name;
    }    
    
    public static List<ContentTemplate> allCT(String name) throws Exception {
        List<ContentTemplate> list = new ArrayList<ContentTemplate>();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String strSQL = "select * from md_content_templates "
                + " where upper(content_template_name) like upper('%" + name + "%') "
                + " order by content_template_name";
        try {
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                ContentTemplate c = new ContentTemplate();
                c.setId(rs.getString(1));
                c.setName(rs.getString(2));
                c.setStatus(rs.getString(3));
                list.add(c);
            }
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return list;
    }

    public static List<ContentTemplate> allCTNotIn(String product_id) throws Exception {
        List<ContentTemplate> list = new ArrayList<ContentTemplate>();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String strSQL = "select * from md_content_templates "
                + " where content_template_id not in "
                + " ( "
                + " select content_template_id from sdp_product_contents where product_id = " + product_id + ""
                + " )";
        try {
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            while (rs.next()) {
                ContentTemplate c = new ContentTemplate();
                c.setId(rs.getString(1));
                c.setName(rs.getString(2));
                c.setStatus(rs.getString(3));
                list.add(c);
            }
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return list;
    }

    public String getNameById() throws Exception {
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String strSQL = "select * from md_content_templates where content_template_id = " + getId() + " ";
        String kq = "";
        try {
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            if (rs.next()) {
                kq = rs.getString(2);
            }
            return kq;
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
    }
    public static String getNameById(String id) throws Exception {
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String kq = "";
        if(id == null){
            return kq;
        }
        String strSQL = "select * from md_content_templates where content_template_id = " + id + " ";        
        try {
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            if (rs.next()) {
                kq = rs.getString(2);
            }
            return kq;
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
    }

    public String getIdByName() throws Exception {
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String strSQL = "select * from md_content_templates where LOWER(content_template_name) = '" + getName().toLowerCase() + "' ";
        String kq = "";
        try {
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            if (rs.next()) {
                kq = rs.getString(1);
            }
            return kq;
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
    }   
    public static String getIdByName(String name) throws Exception {
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String kq = "";
        if(name == null){
            return kq;
        }
        String strSQL = "select * from md_content_templates where LOWER(content_template_name) = '" + name.toLowerCase() + "' ";        
        try {
            cn = AppServer.getConnection();
            st = cn.createStatement();
            rs = st.executeQuery(strSQL);
            if (rs.next()) {
                kq = rs.getString(1);
            }
            return kq;
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
    }

    public boolean insert() throws Exception {
        Connection cn = null;
        PreparedStatement pst = null;

        try {
            cn = AppServer.getConnection();
            setId(Database.getSequenceValue(cn, "seq_md_content_template"));
            String strSQL = " insert into md_content_templates "
                    + " values(?,?,?) ";
            pst = cn.prepareStatement(strSQL);

            pst.setInt(1, Integer.parseInt(getId()));
            pst.setString(2, getName());
            pst.setInt(3, Integer.parseInt(getStatus()));

            return pst.executeUpdate() > 0;
        } finally {
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
    }

    public boolean update() throws Exception {
        Connection cn = null;
        PreparedStatement pst = null;
        String strSQL = " update md_content_templates "
                + " set content_template_name = ?, status = ? "
                + " where content_template_id = ? ";
        try {
            cn = AppServer.getConnection();
            pst = cn.prepareCall(strSQL);

            pst.setString(1, getName());
            pst.setInt(2, Integer.parseInt(getStatus()));
            pst.setInt(3, Integer.parseInt(getId()));

            return pst.executeUpdate() > 0;

        } finally {
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
    }

    public boolean remove() throws Exception {
        Connection cn = null;
        PreparedStatement stmt = null;

        String strSQL = " delete from md_content_templates where content_template_name = '" + getName() + "' ";
        try {
            cn = AppServer.getConnection();
            stmt = cn.prepareStatement(strSQL);
            return stmt.executeUpdate() > 0;
        } finally {
            Database.closeObject(stmt);
            Database.closeObject(cn);
        }
    }
}
